using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
	/// <summary> 
	///Author: nnamthach@gmail.com 
	/// <summary>
    public class SALFLDGDataAccess : Connection
    {
		#region Local Variable
        private string _strSPInsertName = "dbo.[procSALFLDGPRV_add]";
        private string _strSPUpdateName = "dbo.[procSALFLDGPRV_update]";
        private string _strSPDeleteName = "dbo.[procSALFLDGPRV_delete]";
        private string _strSPGetName = "dbo.[procSALFLDGPRV_get]";
        private string _strSPGetAllName = "dbo.[procSALFLDGPRV_getall]";
		private string _strSPGetPages = "dbo.[procSALFLDGPRV_getpaged]";
		private string _strSPIsExist = "dbo.[procSALFLDGPRV_isexist]";
        private string _strSPIsExistHVJ = "dbo.[procSALFLDGHVJ_isexist]";
        private string _strTableName = "SALFLDGPRV";
		#endregion Local Variable
		
		#region Method
        public SALFLDGInfo Get_SALFLDG(
			String ACCNT_CODE
		, ref string sErr)
        {
			SALFLDGInfo objEntr = new SALFLDGInfo();
			InitConnect();
			InitSPCommand(_strSPGetName);              
			AddParameter("ACCNT_CODE", ACCNT_CODE);
            
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();                
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}  
            
            if (list.Rows.Count > 0)
                objEntr = (SALFLDGInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            SALFLDGInfo result = new SALFLDGInfo();
			result.ACCNT_CODE = (dt.Rows[i]["ACCNT_CODE"] == DBNull.Value ? "" : (String)dt.Rows[i]["ACCNT_CODE"]);
			result.PERIOD = (dt.Rows[i]["PERIOD"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["PERIOD"]);
			result.TRANS_DATE = (dt.Rows[i]["TRANS_DATE"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["TRANS_DATE"]);
			result.JRNAL_NO = (dt.Rows[i]["JRNAL_NO"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["JRNAL_NO"]);
			result.JRNAL_LINE = (dt.Rows[i]["JRNAL_LINE"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["JRNAL_LINE"]);
			result.AMOUNT = (dt.Rows[i]["AMOUNT"] == DBNull.Value ? 0 : (Decimal)dt.Rows[i]["AMOUNT"]);
			result.D_C = (dt.Rows[i]["D_C"] == DBNull.Value ? "" : (String)dt.Rows[i]["D_C"]);
			result.ALLOCATION = (dt.Rows[i]["ALLOCATION"] == DBNull.Value ? "" : (String)dt.Rows[i]["ALLOCATION"]);
			result.JRNAL_TYPE = (dt.Rows[i]["JRNAL_TYPE"] == DBNull.Value ? "" : (String)dt.Rows[i]["JRNAL_TYPE"]);
			result.JRNAL_SRCE = (dt.Rows[i]["JRNAL_SRCE"] == DBNull.Value ? "" : (String)dt.Rows[i]["JRNAL_SRCE"]);
			result.TREFERENCE = (dt.Rows[i]["TREFERENCE"] == DBNull.Value ? "" : (String)dt.Rows[i]["TREFERENCE"]);
			result.DESCRIPTN = (dt.Rows[i]["DESCRIPTN"] == DBNull.Value ? "" : (String)dt.Rows[i]["DESCRIPTN"]);
			result.ENTRY_DATE = (dt.Rows[i]["ENTRY_DATE"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["ENTRY_DATE"]);
			result.ENTRY_PRD = (dt.Rows[i]["ENTRY_PRD"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["ENTRY_PRD"]);
			result.DUE_DATE = (dt.Rows[i]["DUE_DATE"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["DUE_DATE"]);
			result.ALLOC_REF = (dt.Rows[i]["ALLOC_REF"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["ALLOC_REF"]);
			result.ALLOC_DATE = (dt.Rows[i]["ALLOC_DATE"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["ALLOC_DATE"]);
			result.ALLOC_PERIOD = (dt.Rows[i]["ALLOC_PERIOD"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["ALLOC_PERIOD"]);
			result.ASSET_IND = (dt.Rows[i]["ASSET_IND"] == DBNull.Value ? "" : (String)dt.Rows[i]["ASSET_IND"]);
			result.ASSET_CODE = (dt.Rows[i]["ASSET_CODE"] == DBNull.Value ? "" : (String)dt.Rows[i]["ASSET_CODE"]);
			result.ASSET_SUB = (dt.Rows[i]["ASSET_SUB"] == DBNull.Value ? "" : (String)dt.Rows[i]["ASSET_SUB"]);
			result.CONV_CODE = (dt.Rows[i]["CONV_CODE"] == DBNull.Value ? "" : (String)dt.Rows[i]["CONV_CODE"]);
			result.CONV_RATE = (dt.Rows[i]["CONV_RATE"] == DBNull.Value ? 0 : (Decimal)dt.Rows[i]["CONV_RATE"]);
			result.OTHER_AMT = (dt.Rows[i]["OTHER_AMT"] == DBNull.Value ? 0 : (Decimal)dt.Rows[i]["OTHER_AMT"]);
			result.OTHER_DP = (dt.Rows[i]["OTHER_DP"] == DBNull.Value ? "" : (String)dt.Rows[i]["OTHER_DP"]);
			result.CLEARDOWN = (dt.Rows[i]["CLEARDOWN"] == DBNull.Value ? "" : (String)dt.Rows[i]["CLEARDOWN"]);
			result.REVERSAL = (dt.Rows[i]["REVERSAL"] == DBNull.Value ? "" : (String)dt.Rows[i]["REVERSAL"]);
			result.LOSS_GAIN = (dt.Rows[i]["LOSS_GAIN"] == DBNull.Value ? "" : (String)dt.Rows[i]["LOSS_GAIN"]);
			result.ROUGH_FLAG = (dt.Rows[i]["ROUGH_FLAG"] == DBNull.Value ? "" : (String)dt.Rows[i]["ROUGH_FLAG"]);
			result.IN_USE_FLAG = (dt.Rows[i]["IN_USE_FLAG"] == DBNull.Value ? "" : (String)dt.Rows[i]["IN_USE_FLAG"]);
			result.ANAL_T0 = (dt.Rows[i]["ANAL_T0"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_T0"]);
			result.ANAL_T1 = (dt.Rows[i]["ANAL_T1"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_T1"]);
			result.ANAL_T2 = (dt.Rows[i]["ANAL_T2"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_T2"]);
			result.ANAL_T3 = (dt.Rows[i]["ANAL_T3"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_T3"]);
			result.ANAL_T4 = (dt.Rows[i]["ANAL_T4"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_T4"]);
			result.ANAL_T5 = (dt.Rows[i]["ANAL_T5"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_T5"]);
			result.ANAL_T6 = (dt.Rows[i]["ANAL_T6"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_T6"]);
			result.ANAL_T7 = (dt.Rows[i]["ANAL_T7"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_T7"]);
			result.ANAL_T8 = (dt.Rows[i]["ANAL_T8"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_T8"]);
			result.ANAL_T9 = (dt.Rows[i]["ANAL_T9"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_T9"]);
			result.POSTING_DATE = (dt.Rows[i]["POSTING_DATE"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["POSTING_DATE"]);
			result.ALLOC_IN_PROGRESS = (dt.Rows[i]["ALLOC_IN_PROGRESS"] == DBNull.Value ? "" : (String)dt.Rows[i]["ALLOC_IN_PROGRESS"]);
			result.HOLD_REF = (dt.Rows[i]["HOLD_REF"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["HOLD_REF"]);
			result.HOLD_OP_ID = (dt.Rows[i]["HOLD_OP_ID"] == DBNull.Value ? "" : (String)dt.Rows[i]["HOLD_OP_ID"]);
           
            return result;
        }

        public DataTable GetAll_SALFLDG(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllName);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
		/// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add_SALFLDG(SALFLDGInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPInsertName);           
			AddParameter("ACCNT_CODE", objEntr.ACCNT_CODE);
			AddParameter("PERIOD", objEntr.PERIOD);
			AddParameter("TRANS_DATE", objEntr.TRANS_DATE);
			AddParameter("JRNAL_NO", objEntr.JRNAL_NO);
			AddParameter("JRNAL_LINE", objEntr.JRNAL_LINE);
			AddParameter("AMOUNT", objEntr.AMOUNT);
			AddParameter("D_C", objEntr.D_C);
			AddParameter("ALLOCATION", objEntr.ALLOCATION);
			AddParameter("JRNAL_TYPE", objEntr.JRNAL_TYPE);
			AddParameter("JRNAL_SRCE", objEntr.JRNAL_SRCE);
			AddParameter("TREFERENCE", objEntr.TREFERENCE);
			AddParameter("DESCRIPTN", objEntr.DESCRIPTN);
			AddParameter("ENTRY_DATE", objEntr.ENTRY_DATE);
			AddParameter("ENTRY_PRD", objEntr.ENTRY_PRD);
			AddParameter("DUE_DATE", objEntr.DUE_DATE);
			AddParameter("ALLOC_REF", objEntr.ALLOC_REF);
			AddParameter("ALLOC_DATE", objEntr.ALLOC_DATE);
			AddParameter("ALLOC_PERIOD", objEntr.ALLOC_PERIOD);
			AddParameter("ASSET_IND", objEntr.ASSET_IND);
			AddParameter("ASSET_CODE", objEntr.ASSET_CODE);
			AddParameter("ASSET_SUB", objEntr.ASSET_SUB);
			AddParameter("CONV_CODE", objEntr.CONV_CODE);
			AddParameter("CONV_RATE", objEntr.CONV_RATE);
			AddParameter("OTHER_AMT", objEntr.OTHER_AMT);
			AddParameter("OTHER_DP", objEntr.OTHER_DP);
			AddParameter("CLEARDOWN", objEntr.CLEARDOWN);
			AddParameter("REVERSAL", objEntr.REVERSAL);
			AddParameter("LOSS_GAIN", objEntr.LOSS_GAIN);
			AddParameter("ROUGH_FLAG", objEntr.ROUGH_FLAG);
			AddParameter("IN_USE_FLAG", objEntr.IN_USE_FLAG);
			AddParameter("ANAL_T0", objEntr.ANAL_T0);
			AddParameter("ANAL_T1", objEntr.ANAL_T1);
			AddParameter("ANAL_T2", objEntr.ANAL_T2);
			AddParameter("ANAL_T3", objEntr.ANAL_T3);
			AddParameter("ANAL_T4", objEntr.ANAL_T4);
			AddParameter("ANAL_T5", objEntr.ANAL_T5);
			AddParameter("ANAL_T6", objEntr.ANAL_T6);
			AddParameter("ANAL_T7", objEntr.ANAL_T7);
			AddParameter("ANAL_T8", objEntr.ANAL_T8);
			AddParameter("ANAL_T9", objEntr.ANAL_T9);
			AddParameter("POSTING_DATE", objEntr.POSTING_DATE);
			AddParameter("ALLOC_IN_PROGRESS", objEntr.ALLOC_IN_PROGRESS);
			AddParameter("HOLD_REF", objEntr.HOLD_REF);
			AddParameter("HOLD_OP_ID", objEntr.HOLD_OP_ID);
          
            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if(tmp != null && tmp != DBNull.Value)
					ret = Convert.ToInt32(tmp);
				else 
					ret=0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
			
            return ret;
        }

        public string Update_SALFLDG(SALFLDGInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateName); 
                       
			AddParameter("ACCNT_CODE", objEntr.ACCNT_CODE);
			AddParameter("PERIOD", objEntr.PERIOD);
			AddParameter("TRANS_DATE", objEntr.TRANS_DATE);
			AddParameter("JRNAL_NO", objEntr.JRNAL_NO);
			AddParameter("JRNAL_LINE", objEntr.JRNAL_LINE);
			AddParameter("AMOUNT", objEntr.AMOUNT);
			AddParameter("D_C", objEntr.D_C);
			AddParameter("ALLOCATION", objEntr.ALLOCATION);
			AddParameter("JRNAL_TYPE", objEntr.JRNAL_TYPE);
			AddParameter("JRNAL_SRCE", objEntr.JRNAL_SRCE);
			AddParameter("TREFERENCE", objEntr.TREFERENCE);
			AddParameter("DESCRIPTN", objEntr.DESCRIPTN);
			AddParameter("ENTRY_DATE", objEntr.ENTRY_DATE);
			AddParameter("ENTRY_PRD", objEntr.ENTRY_PRD);
			AddParameter("DUE_DATE", objEntr.DUE_DATE);
			AddParameter("ALLOC_REF", objEntr.ALLOC_REF);
			AddParameter("ALLOC_DATE", objEntr.ALLOC_DATE);
			AddParameter("ALLOC_PERIOD", objEntr.ALLOC_PERIOD);
			AddParameter("ASSET_IND", objEntr.ASSET_IND);
			AddParameter("ASSET_CODE", objEntr.ASSET_CODE);
			AddParameter("ASSET_SUB", objEntr.ASSET_SUB);
			AddParameter("CONV_CODE", objEntr.CONV_CODE);
			AddParameter("CONV_RATE", objEntr.CONV_RATE);
			AddParameter("OTHER_AMT", objEntr.OTHER_AMT);
			AddParameter("OTHER_DP", objEntr.OTHER_DP);
			AddParameter("CLEARDOWN", objEntr.CLEARDOWN);
			AddParameter("REVERSAL", objEntr.REVERSAL);
			AddParameter("LOSS_GAIN", objEntr.LOSS_GAIN);
			AddParameter("ROUGH_FLAG", objEntr.ROUGH_FLAG);
			AddParameter("IN_USE_FLAG", objEntr.IN_USE_FLAG);
			AddParameter("ANAL_T0", objEntr.ANAL_T0);
			AddParameter("ANAL_T1", objEntr.ANAL_T1);
			AddParameter("ANAL_T2", objEntr.ANAL_T2);
			AddParameter("ANAL_T3", objEntr.ANAL_T3);
			AddParameter("ANAL_T4", objEntr.ANAL_T4);
			AddParameter("ANAL_T5", objEntr.ANAL_T5);
			AddParameter("ANAL_T6", objEntr.ANAL_T6);
			AddParameter("ANAL_T7", objEntr.ANAL_T7);
			AddParameter("ANAL_T8", objEntr.ANAL_T8);
			AddParameter("ANAL_T9", objEntr.ANAL_T9);
			AddParameter("POSTING_DATE", objEntr.POSTING_DATE);
			AddParameter("ALLOC_IN_PROGRESS", objEntr.ALLOC_IN_PROGRESS);
			AddParameter("HOLD_REF", objEntr.HOLD_REF);
			AddParameter("HOLD_OP_ID", objEntr.HOLD_OP_ID);
               
            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_SALFLDG(
			String ACCNT_CODE
		)
        {
            openConnection();
            InitSPCommand(_strSPDeleteName);                        
			AddParameter("ACCNT_CODE", ACCNT_CODE);
              
            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }   
		
		
        
        public Boolean IsExist_SALFLDG(
            String TREFERENCE
		)
        {
            openConnection();
            //InitSPCommand(_strSPIsExist);
            string query = "select * from SALFLDGPRV where TREFERENCE=N'" + (TREFERENCE.Replace("'", "''")) + "'";
            //AddParameter("TREFERENCE", TREFERENCE);
              
            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if(list.Rows.Count==1)
				return true;
            return false;
        }

        public Boolean IsExist_SALFLDGHVJ(
            String TREFERENCE
        )
        {
            openConnection();
            //InitSPCommand(_strSPIsExistHVJ);
            string query = "select * from SALFLDGHVJ where TREFERENCE=N'" + (TREFERENCE.Replace("'", "''")) + "'";
            //AddParameter("TREFERENCE", TREFERENCE);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }
		
		private string CreateWhereClause(SALFLDGInfo obj)
        {
            String result = "";

            return result;
        }
        
        public DataTable Search(string columnName, string columnValue, string condition, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
		#endregion Method
     
    }
}
